﻿
CREATE TABLE ContentQuality
 ( 
	Id int NOT NULL,
	Name nvarchar(50)
)
;

CREATE TABLE ContentType
 ( 
	Id int NOT NULL, 
	MimeType varchar(50) NOT NULL    --  MIME type
)
;

CREATE TABLE storage.Content 
( 
	Id uniqueidentifier DEFAULT newid() NOT NULL,    --  Content GUID 
	MediaTypeId int NOT NULL,    --  Media Type 
	IdentificationURI nvarchar(200) NOT NULL,
	Comment text,    --  Description 
	Version timestamp NOT NULL
)
;

CREATE TABLE storage.StorageFile 
( 
	Id uniqueidentifier DEFAULT newid() NOT NULL,    --  StorageFile GUID 
	StorageId uniqueidentifier NOT NULL,    --  Storage ID 
	ContentId uniqueidentifier NOT NULL,    --  Content GUID Identifier
	Quality int,    --  Quality, (PX, ....)
	ContentType int,    --  ContentTypes 
	Size int,    --  File Size
	MD5Sum varchar(50),    --  MD5 checksum 
	Created datetime,    --  Create Time 
	Modified datetime,    --  Time of last modification
	Version timestamp NOT NULL
)
;

CREATE TABLE storage.MediaType 
( 
	Id int NOT NULL,
	Name nvarchar(200) NOT NULL
)
;

CREATE TABLE storage.rAllowedContentType 
( 
	Id int identity(0,1)  NOT NULL,
	StorageId uniqueidentifier NOT NULL,
	ContentTypeId int NOT NULL
)
;

CREATE TABLE storage.rAllowedMediaType 
( 
	Id int NOT NULL,
	StorageId uniqueidentifier NOT NULL,
	MediaTypeId int NOT NULL
)
;

CREATE TABLE storage.FileStorage 
( 
	Id uniqueidentifier DEFAULT newid() NOT NULL,    -- FileStorage Identifier
	Name nvarchar(100) NOT NULL,    --  FileStorage name 
	DownloadUrlPrefix nvarchar(200),    --  Prefix for file storage deploy documents download method 
	StreamingUrlPrefix nvarchar(200),    --  Prefix for file storage deploy documents stream method
	ServiceEndpoint nvarchar(200) NOT NULL,    --  service endpoint
	Priority int NOT NULL,    --  Priority in save
	Version timestamp NOT NULL
)
;


--  Create Primary Key Constraints 
ALTER TABLE ContentQuality ADD CONSTRAINT PK_ContentQuality 
	PRIMARY KEY CLUSTERED (Id)
;

ALTER TABLE ContentType ADD CONSTRAINT PK_ContentType 
	PRIMARY KEY CLUSTERED (Id)
;

ALTER TABLE storage.Content ADD CONSTRAINT PK_Content
	PRIMARY KEY CLUSTERED (Id)
;

ALTER TABLE storage.StorageFile ADD CONSTRAINT PK_StorageFile 
	PRIMARY KEY CLUSTERED (Id)
;

ALTER TABLE storage.MediaType ADD CONSTRAINT PK_MediaType 
	PRIMARY KEY CLUSTERED (Id)
;

ALTER TABLE storage.rAllowedContentType ADD CONSTRAINT PK_rAllowedContentType 
	PRIMARY KEY CLUSTERED (Id)
;

ALTER TABLE storage.rAllowedMediaType ADD CONSTRAINT PK_rAllowedMediaType 
	PRIMARY KEY CLUSTERED (Id)
;

ALTER TABLE storage.FileStorage ADD CONSTRAINT PK_FileStorage 
	PRIMARY KEY CLUSTERED (Id)
;


--  Create Indexes 
CREATE INDEX idx_storage_MediaTypeId_IdentificationURI
ON storage.Content (MediaTypeId ASC, IdentificationURI ASC)
;

CREATE INDEX idx_rAllowedContentType_StorageId_ContentTypeId
ON storage.rAllowedContentType (StorageId ASC, ContentTypeId ASC)
;

CREATE INDEX idx_rAllowedMediaType_StorageId_MediaTypeId
ON storage.rAllowedMediaType (StorageId ASC, MediaTypeId ASC)
;


--  Create Foreign Key Constraints 
ALTER TABLE storage.Content ADD CONSTRAINT FK_Content_MediaType 
	FOREIGN KEY (MediaTypeId) REFERENCES storage.MediaType (Id)
;

ALTER TABLE storage.StorageFile ADD CONSTRAINT FK_Content_Storage 
	FOREIGN KEY (StorageId) REFERENCES storage.Content (Id)
;

ALTER TABLE storage.StorageFile ADD CONSTRAINT FK_File_ContentQuality 
	FOREIGN KEY (Quality) REFERENCES ContentQuality (Id)
;

ALTER TABLE storage.StorageFile ADD CONSTRAINT FK_Content_ContentType 
	FOREIGN KEY (ContentType) REFERENCES ContentType (Id)
;

ALTER TABLE storage.rAllowedContentType ADD CONSTRAINT FK_rAllowedContentType_ContentType 
	FOREIGN KEY (ContentTypeId) REFERENCES ContentType (Id)
;

ALTER TABLE storage.rAllowedContentType ADD CONSTRAINT FK_rAllowedContentType_Storage 
	FOREIGN KEY (StorageId) REFERENCES storage.Content (Id)
;

ALTER TABLE storage.rAllowedMediaType ADD CONSTRAINT FK_rAllowedMediaType_MediaType 
	FOREIGN KEY (MediaTypeId) REFERENCES storage.MediaType (Id)
;

ALTER TABLE storage.rAllowedMediaType ADD CONSTRAINT FK_rAllowedMediaType_Storage 
	FOREIGN KEY (StorageId) REFERENCES storage.Content (Id)
;